package com.zm.devmag.util;


import java.io.UnsupportedEncodingException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * */
    public class ExcelUtil {
    
		public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
		public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
		public static final String EMPTY = "";
		public static final String POINT =".";
	    public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");    

        private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
        //导出excel
        public static void objListToExcel(String name, Map data, HttpServletResponse response) {
            SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
            //导出excel
            String as = name+ (sdf.format(new Date()));
            String fileName = null;
            try {
                fileName = new String(as.getBytes("GB2312"), "ISO_8859_1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;fileName="+fileName+".xlsx");

        Map<String, String> ziDuan = (Map<String, String>) data.get("ziDuan");
        List listData = (List) data.get("listData");
        Object[] keys = ziDuan.keySet().toArray();
        String[] ziDuanKeys = new String[keys.length];
        for (int k = 0; k < keys.length; k++) {
            String temp = keys[k].toString();
            int xuHao = Integer.valueOf(temp.substring(0,temp.indexOf("-")));
            ziDuanKeys[xuHao] = temp.substring(temp.indexOf("-")+1);
        }
        try {
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet();

            for (int i = 0; i < listData.size()+1; i++) {
                XSSFRow row = sheet.createRow(i);

                for (int j = 0; j < ziDuanKeys.length; j++) {
                    XSSFCell cell = row.createCell(j);
                    if (i == 0) {
                        sheet.setColumnWidth(j, 5000);
                        cell.setCellValue(new XSSFRichTextString(ziDuan.get(j
                                +"-"+ ziDuanKeys[j])));
                    } else {
                        Object obj = listData.get(i-1);
                        String ziDuanName = (String) ziDuanKeys[j];
                        ziDuanName = ziDuanName.replaceFirst(ziDuanName
                                .substring(0, 1), ziDuanName.substring(0,1)
                                .toUpperCase());
                        ziDuanName = "get" + ziDuanName;
                        Class clazz = Class.forName(obj.getClass().getName());
                        Method[] methods = clazz.getMethods();
                        Pattern pattern = Pattern.compile(ziDuanName);
                        Matcher mat = null;
                        for (Method m : methods) {
                            mat = pattern.matcher(m.getName());
                            if (mat.find()) {
                                Object shuXing = m.invoke(obj, null);
                                
                                if (shuXing != null) {
                                    if(shuXing instanceof Date){
                                        SimpleDateFormat sdf2=new SimpleDateFormat("yyyy/MM/dd");
                                        shuXing=sdf2.format(shuXing);
                                    }
                                    if(m.getName().equals("getStatus")){
                                    	if(shuXing.equals("1")){
                                    		cell.setCellValue("正常");
                                    	}else if(shuXing.equals("2")){
                                    		cell.setCellValue("已出库");
                                    	}else if(shuXing.equals("3")){
                                    		cell.setCellValue("已报废");
                                    	}
                                    }else{
                                    	cell.setCellValue(shuXing.toString());//这里可以做数据格式处理
                                    }
                                } else {
                                    cell.setCellValue("");
                                }
                                break;
                            }
                        }
                    }
                }
            }
            ServletOutputStream out = response.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();

        } catch (Exception e) {
           logger.error("导出信息["+ name+ "]出错",e );
        }
    }
    //excel导入
    /** 
     * 获得path的后缀名 
     * @param path 
     * @return 
     */
    public static String getPostfix(String path){
    	if(path==null || EMPTY.equals(path.trim())){
    		return EMPTY;
    		}
    	if(path.contains(POINT)){
    		return path.substring(path.lastIndexOf(POINT)+1,path.length());
    		}
    	return EMPTY;
    }   
    /** 
     * 单元格格式 
     * @param hssfCell 
     * @return 
     */  
    @SuppressWarnings({"static-access","deprecation"})  
    public static String getHValue(HSSFCell hssfCell){  
         if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN){  
             return String.valueOf(hssfCell.getBooleanCellValue());  
         } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {  
             String cellValue = "";  
             if(HSSFDateUtil.isCellDateFormatted(hssfCell)){                  
                 Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue());  
                 cellValue = sdf.format(date);  
             }else{  
                 DecimalFormat df = new DecimalFormat("#.##");  
                 cellValue = df.format(hssfCell.getNumericCellValue());  
                 String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());  
                 if(strArr.equals("00")){  
                     cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));  
                 }    
             }  
             return cellValue;  
         } else {  
            return String.valueOf(hssfCell.getStringCellValue());  
         }  
    }
    /** 
     * 单元格格式 
     * @param xssfCell 
     * @return 
     */
	public static String getXValue(XSSFCell xssfCell){
		if(xssfCell.getCellType()==Cell.CELL_TYPE_BOOLEAN){
			return String.valueOf(xssfCell.getBooleanCellValue());
			}else if(xssfCell.getCellType()==Cell.CELL_TYPE_NUMERIC){
				String cellValue = "";
				if(XSSFDateUtil.isCellDateFormatted(xssfCell)){
					Date date = XSSFDateUtil.getJavaDate(xssfCell.getNumericCellValue());
					cellValue = sdf.format(date);
				}else{
					DecimalFormat df = new DecimalFormat("#.##");
					cellValue = df.format(xssfCell.getNumericCellValue());
					String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());
					if(strArr.equals("00")){
						cellValue = cellValue.substring(0,cellValue.lastIndexOf(POINT));
					}
				}
					return cellValue;
			}else{
				return String.valueOf(xssfCell.getStringCellValue());
    		}
	}
}
    /** 
	 * 自定义xssf日期工具类 
	 * @author lp 
	 * 
	 */
	class XSSFDateUtil extends DateUtil{
		protected static int absoluteDay(Calendar cal, boolean use1904windowing) {
			return DateUtil.absoluteDay(cal, use1904windowing);
			}
		}
